"""
Case Type   : 二级分区
Case Name   : 二级分区表拼音排序测试
Create At   : 2022/2/26
Owner       : @daiguatutu
Description :
    1.创建库，指定编码UTF8,创建表空间
    2.创建range_range二级分区表
    3.插入数据
    4.查询数据nlssort拼音排序
    5.不同语句格式查询数据nlssort拼音排序
    6.查询nlssort后的数据
    7.查询数据nlssort拼音排序
    8.查询数据不同nlssort拼音排序
    9.创建list_list二级分区表
    10.插入数据并查询分区数据nlssort拼音排序
    11.创建hash_hash二级分区表
    12.插入数据并查询分区数据nlssort拼音排序
    13.查询分区数据nlssort拼音排序
    14.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功，等价上一条语句
    6.成功
    7.成功
    8.成功,不等价上一条语句
    9.成功
    10.成功
    11.成功
    12.成功
    13.成功,第一列相同的时候,col_2的排序与上一条相反
    14.清理成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class Subpartition(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.primaryNode = Node('PrimaryDbUser')
        self.constant = Constant()
        self.t_name = 't_subpartition_0354'
        self.ts_name = 'ts_subpartition_0354'
        self.db_name = 'db_subpartition_0354'
        self.copy_file = os.path.join(macro.DB_INSTANCE_PATH,
                                      'f_subpartition_0354.dat')

    def test_subpartition(self):
        text = '-----step1:创建库，指定编码UTF8,创建表空间 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop database if exists {self.db_name};" \
            f"create database {self.db_name} encoding 'UTF8' " \
            f"template = template0;" \
            f"drop table if exists {self.t_name};" \
            f"drop tablespace if exists {self.ts_name};" \
            f"create tablespace {self.ts_name} relative location " \
            f"'{self.ts_name}';"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, msg,
                      '执行失败:' + text)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg,
                      '执行失败:' + text)
        self.assertIn(self.constant.TABLESPCE_CREATE_SUCCESS, msg,
                      '执行失败:' + text)

        text = '-----step2:创建range_range二级分区表 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (col_1 char(40), col_2 char(40) , " \
            f"col_3 varchar2 ( 30 ) , col_4 int )tablespace {self.ts_name} " \
            f"partition by range (col_1) subpartition by range (col_2) " \
            f"(partition p_range_1 values less than('a') " \
            f"(subpartition p_range_1_1 values less than( 5 )," \
            f"subpartition p_range_1_2 values less than( 50 )," \
            f"subpartition p_range_1_3 values less than( maxvalue ))," \
            f"partition p_range_2 values less than( 'b' )" \
            f"(subpartition p_range_2_1 values less than( 0 )," \
            f"subpartition p_range_2_2 values less than( 10 )," \
            f"subpartition p_range_2_3 values less than( 20 )," \
            f"subpartition p_range_2_4 values less than( 30 )," \
            f"subpartition p_range_2_5 values less than( maxvalue ))," \
            f"partition p_range_3 values less than( 'c')" \
            f"(subpartition p_range_3_5 values less than( maxvalue ))," \
            f"partition p_range_4 values less than( 'd')," \
            f"partition p_range_5 values less than('gong')," \
            f"partition p_range_6 values less than( maxvalue ));"
        msg = self.pri_sh.execut_db_sql(create_sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)

        text = '-----step3:插入数据 expect:成功-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values('工商银行','icbc')," \
            f"('中国银行','BOC'),('建设银行','CCB'),('设银','是否')," \
            f"('银行','稳定');" \
            f"insert into {self.t_name} values('招商银行','还有')," \
            f"('农业银行','稳定'),('知识银行','剂'),('图书馆','被删除'),('图书馆','删除');"
        msg = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)

        text = '-----step4:查询数据nlssort拼音排序 expect:成功-----'
        self.log.info(text)
        sql = f"select * from {self.t_name} order by " \
            f"nlssort(col_1,'nls_sort = schinese_pinyin_m') asc,col_2;"
        select_res1 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res1)
        select_exp1 = ['工商银行', '建设银行', '农业银行', '设银', '图书馆',
                       '图书馆', '银行', '招商银行', '知识银行', '中国银行']
        for i in range(10):
            self.assertIn(select_exp1[i], select_res1.splitlines()[-(11 - i)],
                          '执行失败:' + text)

        text = '-----step5:不同语句格式查询数据nlssort拼音排序 expect:成功，等价上一条语句-----'
        self.log.info(text)
        sql = f"select * from (select * from {self.t_name} order by nlssort" \
            f"(col_1,'nls_sort = schinese_pinyin_m') desc,col_2 desc) " \
            f"order by nlssort(col_1,'nls_sort = schinese_pinyin_m') " \
            f"asc,col_2 asc;"
        select_res2 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res2)
        self.assertEqual(select_res1, select_res2, '执行失败:' + text)

        text = '-----step6:查询nlssort后的数据 expect:成功-----'
        self.log.info(text)
        sql = f"select nlssort(col_2,'nls_sort = schinese_pinyin_m') " \
            f"from {self.t_name};"
        select_res3 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res3)
        select_exp3 = ['021B01F401EF01F40000020202020002020202',
                       '01EF023801F4000002020200060707',
                       '01F401F401EF000002020200070706',
                       '972955B500000101000101', 'A4654FBC00000101000101',
                       '6145B85C00000101000101', 'A4654FBC00000101000101',
                       '65640000010001', '3E45942E4861000001010100010101',
                       '942E486100000101000101']
        for i in range(10):
            self.assertIn(select_exp3[i], select_res3.splitlines()[-(11 - i)],
                          '执行失败:' + text)

        text = '-----step7:查询数据nlssort拼音排序; expect:成功-----'
        self.log.info(text)
        sql = f"select * from {self.t_name} order by " \
            f"nlssort(col_1,'nls_sort = schinese_pinyin_m') asc,col_2;"
        select_res4 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res4)
        for i in range(10):
            self.assertIn(select_exp1[i], select_res4.splitlines()[-(11 - i)],
                          '执行失败:' + text)

        text = '-----step8:查询数据不同nlssort拼音排序; expect:成功,不等价上一条语句-----'
        self.log.info(text)
        sql = f"select * from {self.t_name} order by " \
            f"nlssort(1,'NLS_SORT = SCHINESE_PINYIN_M') asc,col_2;"
        select_res5 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res5)
        self.assertNotEqual(select_res4, select_res5, '执行失败:' + text)

        text = '-----step9:创建list_list二级分区表 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (col_1 VARCHAR2 ( 30 ), " \
            f"col_2 VARCHAR2 ( 30 ) not null, col_3 varchar2 ( 30 ) , " \
            f"col_4 VARCHAR2 ( 30 ) )with(FILLFACTOR=80)" \
            f"partition by list (col_1) subpartition by list (col_2)" \
            f"(partition p_list_1 values('图书馆')" \
            f"(subpartition p_list_1_1 values ('被删除')," \
            f"subpartition p_list_1_2 values ( default ))," \
            f"partition p_list_2 values('银行')" \
            f"(subpartition p_list_2_1 values ('还有')," \
            f"subpartition p_list_2_2 values ( default )," \
            f"subpartition p_list_2_3 values ('删除')," \
            f"subpartition p_list_2_4 values ( 'ccb')," \
            f"subpartition p_list_2_5 values ('icbc'))," \
            f"partition p_list_3 values('农业银行')" \
            f"(subpartition p_list_3_2 values ( default ))," \
            f"partition p_list_4 values(default )," \
            f"partition p_list_5 values('知识银行')" \
            f"(subpartition p_list_5_1 values ( '剂')," \
            f"subpartition p_list_5_2 values ( default )," \
            f"subpartition p_list_5_3 values ('被删除')," \
            f"subpartition p_list_5_4 values ('删除')," \
            f"subpartition p_list_5_5 values ('icbc'))," \
            f"partition p_list_6 values('中国银行')," \
            f"partition p_list_7 values('招商银行')" \
            f"(subpartition p_list_7_1 values ( default ))" \
            f") enable row movement;"
        msg = self.pri_sh.execut_db_sql(create_sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)

        text = '-----step10:插入数据并查询分区数据nlssort拼音排序 expect:成功-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values('工商银行','icbc')," \
            f"('中国银行','BOC'),('建设银行','CCB'),('设银','是否')," \
            f"('银行','稳定');" \
            f"insert into {self.t_name} values('招商银行','还有')," \
            f"('农业银行','稳定'),('知识银行','剂'),('图书馆','被删除'),('图书馆','删除');" \
            f"select * from {self.t_name} subpartition" \
            f"(p_list_4_subpartdefault1) order by " \
            f"nlssort(col_1,'nls_sort = schinese_pinyin_m') asc,col_2;"
        msg = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)
        self.assertIn('3 rows', msg, '执行失败:' + text)
        self.assertIn('工商银行', msg.splitlines()[-4], '执行失败:' + text)
        self.assertIn('建设银行', msg.splitlines()[-3], '执行失败:' + text)
        self.assertIn('设银', msg.splitlines()[-2], '执行失败:' + text)

        text = '-----step11:创建hash_hash二级分区表 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (col_1 VARCHAR2 ( 30 ), " \
            f"col_2 VARCHAR2 ( 30 ) not null, col_3 varchar2 ( 30 ) , " \
            f"col_4 VARCHAR2 ( 30 ) )with(FILLFACTOR=80)" \
            f"partition by hash (col_1) subpartition by hash (col_2)" \
            f"(partition p_range_1 " \
            f"(subpartition p_hash_1_1 ,subpartition p_hash_1_2 ," \
            f" subpartition p_hash_1_3,subpartition p_hash_1_4," \
            f" subpartition p_hash_1_5,subpartition p_hash_1_6)," \
            f"partition p_range_2," \
            f"partition p_range_3 " \
            f"(subpartition p_hash_3_1 ,subpartition p_hash_3_2 ," \
            f" subpartition p_hash_3_3)," \
            f"partition p_range_4" \
            f"(subpartition p_hash_4_1)," \
            f"partition p_range_5" \
            f") enable row movement;"
        msg = self.pri_sh.execut_db_sql(create_sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)

        text = '-----step12:插入数据并查询分区数据nlssort拼音排序 expect:成功-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values('工商银行','icbc')," \
            f"('中国银行','BOC'),('建设银行','CCB'),('设银','是否')," \
            f"('银行','稳定');" \
            f"insert into {self.t_name} values('招商银行','还有')," \
            f"('农业银行','稳定'),('知识银行','剂'),('图书馆','被删除'),('图书馆','删除');" \
            f"select * from {self.t_name} subpartition(p_hash_4_1) " \
            f"order by nlssort(col_1,'NLS_SORT = SCHINESE_PINYIN_M')," \
            f"nlssort(col_2,'NLS_SORT = SCHINESE_PINYIN_M');"
        msg = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)
        self.assertIn('4 rows', msg, '执行失败:' + text)
        self.assertIn('农业银行 | 稳定', msg.splitlines()[-5], '执行失败:' + text)
        self.assertIn('图书馆   | 被删除', msg.splitlines()[-4], '执行失败:' + text)
        self.assertIn('图书馆   | 删除', msg.splitlines()[-3], '执行失败:' + text)
        self.assertIn('知识银行 | 剂', msg.splitlines()[-2], '执行失败:' + text)

        text = '-----step13:查询分区数据nlssort拼音排序 ' \
               'expect:成功,第一列相同的时候,col_2的排序与上一条相反-----'
        self.log.info(text)
        sql = f"select * from {self.t_name} subpartition(p_hash_4_1) " \
            f"order by nlssort(col_1,'NLS_SORT = SCHINESE_PINYIN_M')," \
            f"nlssort(col_2,'NLS_SORT = SCHINESE_PINYIN_M') desc;"
        msg = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertIn('4 rows', msg, '执行失败:' + text)
        self.assertIn('农业银行 | 稳定', msg.splitlines()[-5], '执行失败:' + text)
        self.assertIn('图书馆   | 删除', msg.splitlines()[-4], '执行失败:' + text)
        self.assertIn('图书馆   | 被删除', msg.splitlines()[-3], '执行失败:' + text)
        self.assertIn('知识银行 | 剂', msg.splitlines()[-2], '执行失败:' + text)

    def tearDown(self):
        self.log.info('-----step14:清理环境-----')
        text_1 = '-----删除表和库 expect:成功-----'
        self.log.info(text_1)
        del_cmd = f'drop table if exists {self.t_name};' \
            f'drop database if exists {self.db_name};'
        del_msg = self.pri_sh.execut_db_sql(del_cmd)
        self.log.info(del_msg)

        text_2 = '-----删除copy文件 expect:成功-----'
        self.log.info(text_2)
        rm_cmd = f'rm -rf {self.copy_file};cat {self.copy_file};'
        rm_msg = self.primaryNode.sh(rm_cmd).result()
        self.log.info(rm_msg)

        text_3 = '-----删除表空间 expect:成功-----'
        self.log.info(text_3)
        drop_cmd = f"drop tablespace if exists {self.ts_name};"
        drop_msg = self.pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_msg)

        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, del_msg,
                      '执行失败:' + text_1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, del_msg,
                      '执行失败:' + text_1)
        self.assertIn(self.constant.NO_FILE_MSG, rm_msg, '执行失败:' + text_2)
        self.assertEqual(self.constant.TABLESPCE_DROP_SUCCESS, drop_msg,
                         '执行失败:' + text_3)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
